﻿using System;
using System.Collections.Generic;
using System.Data;
using MySqlConnector;
using System.Data.Common;
using DotNetCommon.Extensions;
using DBUtil.SqlSegment;
using DBUtil.Provider.MySql.SqlSegment;
using DotNetCommon.Data;
using System.Reflection;
using DotNetCommon.Logger;
using System.Linq;
using System.Text.Json.Serialization.Metadata;

namespace DBUtil.Provider.MySql
{
    /// <summary>
    /// MySql操作对象
    /// </summary>
    public partial class MySqlAccess : DBAccess
    {
        private static ILogger<MySqlAccess> logger = LoggerFactory.CreateLogger<MySqlAccess>();

        protected override DbConnection GetConnectionByConnectString() => new MySqlConnection(DBConn);

        #region 表名或标识符分析/解析
        /// <summary>
        /// 标识符引用标记: [],""
        /// </summary>
        public override List<string> QuoteIdentifierCharacters
        {
            get => new List<string>() { "``", "\"\"" };
        }
        #endregion

        #region 一次最大插入行数
        public override int InsertRowsMaxCountPerBatch { get { return 5000; } }
        #endregion

        #region 创建DataAdapter
        /// <summary>
        /// 创建IDataAdapter
        /// </summary>
        /// <param name="cmd">命令对象</param>
        /// <returns></returns>
        protected override DataAdapter CreateAdapter(DbCommand cmd)
        {
            return new MySqlDataAdapter(cmd as MySqlCommand);
        }
        #endregion

        #region CreatePara 创建参数
        /// <summary>
        /// 创建参数
        /// </summary>
        /// <returns></returns>
        public override DbParameter CreatePara()
        {
            return new MySqlParameter();
        }
        #endregion

        #region 初始化
        private static Lazy<string> appName = new Lazy<string>(() =>
        {
            var name = $"DBUtil-{Assembly.GetEntryAssembly().GetName().Name}";
            if (name.Length > 100) name = name.Substring(0, 100);
            return name;
        });
        /// <summary>
        /// 初始化
        /// </summary>
        /// <param name="DBConn">连接字符串</param>
        /// <param name="Settings">设置</param>
        public MySqlAccess(string DBConn, DBSetting Settings) : base(DBType.MYSQL, resetConnectionString(DBConn), "@", false, Settings)
        {
            InitHooks();
        }

        private static string resetConnectionString(string connectionString)
        {
            var builder = new MySqlConnectionStringBuilder();
            var defaultAppname = builder.ApplicationName;
            builder.ConnectionString = connectionString;
            if (defaultAppname == builder.ApplicationName)
            {
                builder.ApplicationName = appName.Value;
            }
            builder.AllowUserVariables = true;
            return builder.ConnectionString;
        }
        #endregion

        #region 获取当前db/schema/user的sqlseg
        public override string GetCurrentSchemaSqlSeg() => "database()";
        public override string GetCurrentDataBaseSqlSeg() => "database()";
        public override string GetCurrentUserSqlSeg(bool isPure = false) => isPure ? "reverse(substring(reverse(user()),instr(reverse(user()),'@')+1))" : "user()";
        public override string GetCurrentLoginUserSqlSeg(bool isPure = false) => isPure ? "reverse(substring(reverse(user()),instr(reverse(user()),'@')+1))" : "user()";
        public override string GetCurrentDataBaseVersionSqlSeg() => "version()";
        #endregion

        #region 将任意对象转为可拼接的sql ConvertToSqlSeg
        public override Result<string> ConvertToSqlSeg(object obj, object args = null)
        {
            if (obj is DateTimeOffset dateTimeOffset)
            {
                //mysql 8.0.19及以上才允许insert时加时区 且时间和时区之间不能有空格
                //如：2022-02-11 00:30:01+00:00是正确的，而 2022-02-11 00:30:01 +00:00则会报错
                var seg = dateTimeOffset.ToString("yyyy-MM-dd HH:mm:ss.ffffff").TrimEnd('0').Trim('.');
                if (seg.EndsWith(" 00:00:00")) seg = seg.Substring(0, seg.Length - " 00:00:00".Length);
                return Result.Ok($"'{seg}{dateTimeOffset.ToString("zzz")}'");
            }
            if (obj is DateTime dateTime)
            {
                //mysql 最多支持6位小数 超出的自动截断
                string seg;
                if (args is string s) seg = dateTime.ToString(s);
                else seg = dateTime.ToString("yyyy-MM-dd HH:mm:ss.ffffff");
                seg = seg.TrimEnd('0').TrimEnd('.');
                if (seg.EndsWith(" 00:00:00")) seg = seg.Substring(0, seg.Length - " 00:00:00".Length);
                return Result.Ok($"'{seg}'");
            }
            if (obj is TimeOnly timeOnly) obj = timeOnly.ToTimeSpan();
            if (obj is TimeSpan timeSpan)
            {
                //mysql中使用 time 存储 范围是 '-838:59:59.000000' to '838:59:59.000000'
                //精度最大是小数点6位 超出的自动截断
                //注意: 天数需要加到小时上
                if (timeSpan.Days > 0)
                {
                    //c#的 TimeSpan.Parse("12.1:2:3.123456").ToString() => 12.01:02:03.123456
                    //而到了 mysql 应该是: 289:02:03.123456
                    var str = timeSpan.ToString();
                    var idx = str.IndexOf(':');
                    return Result.Ok($"'{timeSpan.Days * 24 + timeSpan.Hours}:{str.Substring(idx + 1)}'");
                }
                else return Result.Ok($"'{timeSpan}'");
            }
            var res = base.ConvertToSqlSeg(obj, args);
            if (res.Success) return res;

            //TODO 
            return Result.NotOk($"未能转换 {obj.GetType().FullName} 类型数据到sql!");
        }
        #endregion

        #region GetSqlForPageSize
        /// <summary>获得分页的查询语句
        /// </summary>
        /// <param name="selectSql">查询sql如:select id,name from person where age>10</param>
        /// <param name="orderSql">排序字句如:order by id</param>
        /// <param name="pageSize">页面大小,如:10</param>
        /// <param name="pageIndex">页面索引从1开始,如:1</param>
        /// <returns>返回示例:select id,name from person where age>10 order by id limit 0,10 </returns>
        public override string GetSqlForPageSize(string selectSql, string orderSql, int pageSize, int pageIndex)
        {
            string sql = string.Format("{0} {1} limit {2},{3}", selectSql, orderSql, (pageIndex - 1) * pageSize, pageSize);
            return sql;
        }
        #endregion

        #region Is系列: 判断表/视图/列/存储过程是否存在
        private string IsProcedureExistSql(string procName)
        {
            procName = EscapeString(procName);
            var objName = ParseObjectName(procName);

            string sql = $"show PROCEDURE status where name='{objName.Name}' and `type` = 'PROCEDURE'";
            if (objName.SegCount >= 2) sql += $" and Db='{objName.SchemaName}'";
            else sql += $" and Db={GetCurrentSchemaSqlSeg()}";
            return sql;
        }
        public override bool IsProcedureExist(string procName)
        {
            var sql = IsProcedureExistSql(procName);
            var r = SelectScalar<string>(sql);
            return r.IsNotNullOrEmptyOrWhiteSpace();
        }

        private string IsTriggerExistSql(string triggerName)
        {
            triggerName = EscapeString(triggerName);
            var objName = ParseObjectName(triggerName);

            var sql = $"select count(1) from information_schema.TRIGGERS t where t.TRIGGER_NAME='{objName.Name}'";
            if (objName.SegCount >= 2) sql += $" t.TRIGGER_SCHEMA = '{objName.SchemaName}'";
            else sql += $" Db={GetCurrentSchemaSqlSeg()}";
            return sql;
        }
        public override bool IsTriggerExist(string triggerName)
        {
            var sql = IsTriggerExistSql(triggerName);
            return SelectScalar<int>(sql) > 0;
        }
        #endregion

        #region 数据库管理对象 Manage
        private DBManage _dbmanage = null;
        /// <summary>
        /// 数据库管理对象
        /// </summary>
        public override DBManage Manage => _dbmanage != null ? _dbmanage : _dbmanage = new MySqlManage(this);
        #endregion

        #region 运算符
        /// <summary>
        /// 数据库日期时间运算符
        /// </summary>
        protected override DateTimeSqlSegment GetDateTimeSqlSegment() => new MySqlDateTimeSqlSegment(this);
        protected override StringSqlSegment GetStringSqlSegment() => new MySqlStringSqlSegment(this);
        protected override ConvertSqlSegment GetConvertSqlSegment() => new MySqlConvertSqlSegment(this);
        #endregion

        #region 使用SqlBulkCopy批量插入数据
        public override void BulkCopy(DataTable dt, string tableName = null, int timeoutSeconds = 60 * 30, int notifyAfter = 0, Func<long, bool> callBack = null)
        {
            if (string.IsNullOrWhiteSpace(tableName)) tableName = dt.TableName;
            if (string.IsNullOrWhiteSpace(tableName)) throw new Exception("必须指定要目的表名!");
            MySqlBulkCopy sbc = null;
            if (IsTransaction) sbc = new MySqlBulkCopy((MySqlConnection)CurrentConnection, (MySqlTransaction)CurrentTransaction);
            else if (IsSession) sbc = new MySqlBulkCopy((MySqlConnection)CurrentConnection);
            else sbc = new MySqlBulkCopy((MySqlConnection)GetNewConnection());

            sbc.BulkCopyTimeout = timeoutSeconds;
            sbc.DestinationTableName = tableName;
            sbc.NotifyAfter = notifyAfter;
            if (callBack != null) sbc.MySqlRowsCopied += (object sender, MySqlRowsCopiedEventArgs e) =>
            {
                e.Abort = callBack(e.RowsCopied);
            };
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                sbc.ColumnMappings.Add(new MySqlBulkCopyColumnMapping(i, dt.Columns[i].ColumnName));
            }
            sbc.WriteToServer(dt);
        }
        #endregion

        #region 获取刚插入的自增id的Sql语句
        public override string GetLastInsertedIdSeg(int len = 1)
        {
            if (len == 1) return $"last_insert_id()";
            return $"last_insert_id()+{len - 1}";
        }
        #endregion

        #region 序列
        /// <summary>
        /// 是否支持序列
        /// </summary>
        /// <returns></returns>
        public override bool IsSupportSequence() => false;

        /// <summary>
        /// 获取 下一个序列值 的sql语句
        /// </summary>
        /// <param name="name">序列名称</param>
        /// <returns></returns>
        public override string NextSequenceValueSeg(string name) => throw new NotSupportedException();
        #endregion

        #region PostDealEntityInfo
        protected override void PostDealEntityInfo(EntityInfo entityInfo)
        {
            var cols = entityInfo.EntityPropertyInfos.Where(i => i.IsColumn).ToList();
            #region 处理 [MySqlColumn]
            for (int i = 0; i < cols.Count; i++)
            {
                var entityPropertyInfo = cols[i];
                var propAttrs = entityPropertyInfo.PropertyInfo.GetCustomAttributes();
                //[MySqlColumn]
                var colAttr = propAttrs.FirstOrDefault(i => i is MySqlColumnAttribute) as MySqlColumnAttribute;
                if (colAttr != null)
                {
                    entityPropertyInfo.ColumnNameSeg = colAttr?.Name ?? entityPropertyInfo.ColumnNameSeg;
                    entityPropertyInfo.ColumnNamePure = RemoveQuote(entityPropertyInfo.ColumnNameSeg);
                    entityPropertyInfo.TypeName = colAttr?.TypeName;
                    // [Column] 源码中默认就是-1
                    entityPropertyInfo.Order = colAttr?.Order ?? -1;
                    entityPropertyInfo.IsDbString = colAttr?.TypeName?.Contains("char") == true || colAttr?.TypeName?.Contains("text") == true;
                }
            }
            #endregion
            #region 处理 SelectExpr
            //https://dev.mysql.com/doc/refman/8.0/en/json-search-functions.html#function_json-value
            //处理 SelectExpr
            /*
            select 
                json_value('{"id":1}','$.id' returning signed)
                ,json_value('{"id":true}','$.id' returning signed)
                ,json_value('{"id":"小明"}','$.id' returning char)
                ,convert(json_value('{"id":"2023-01-28"}','$.id'),datetime(6))
                ,convert(json_value('{"id":"2023-01-28 01:02:03.12345678"}','$.id'),datetime(6))
                ,convert(json_value('{"id":"2023-01-28 01:02:03.12345678+09:00"}','$.id'),datetime(6))
                ,convert(json_value('{"id":"2023-01-28 01:02:03.12345678"}','$.id'),date)
                ,convert(json_value('{"id":"2023-01-28 01:02:03.12345678"}','$.id'),time(6))
                ,json_value('{"id":null}','$.id' returning signed)
                ,json_value('{"id":12345678.12345678}','$.id' returning float) -- 可能需要mysql8.0.17及以上,否则使用 decimal(30,15)
                ,json_value('{"id":12345678.12345678}','$.id' returning double) -- 可能需要mysql8.0.17及以上,否则使用 decimal(30,15)
                ,json_value('{"id":12345678901.12345678}','$.id' returning double) -- 可能需要mysql8.0.17及以上,否则使用 decimal(30,15)
                ,json_value('{"id":12345678901.12345678}','$.id' returning decimal(30,15))
            */
            for (int i = 0; i < cols.Count; i++)
            {
                var col = cols[i];
                col.IsDbString = col.TypeName?.Contains("char");
                if (col.IsDbString != true) col.IsDbString = col.TypeName?.Contains("text");
                var jsonPathWrap = col.JsonKey.IsNullOrEmptyOrWhiteSpace() ? "'$'" : GetJsonPathWrapByPropertyName(col.JsonKey);
                string columnNameFunc(string alias) => alias.IsNotNullOrEmptyOrWhiteSpace() ? $"{alias}.{col.JsonBucket}" : col.JsonBucket;
                if (col.JsonBucket.IsNotNullOrEmptyOrWhiteSpace())
                {
                    if (col.JsonSelectKey.IsNotNullOrEmptyOrWhiteSpace())
                    {
                        //存在 JsonSelectKey 时
                        col.SelectFunc = (string alias) =>
                        {
                            var ret = alias.IsNotNullOrEmptyOrWhiteSpace() ? $"{alias}.{col.JsonSelectKey}" : col.JsonSelectKey;
                            if (col.JsonSelectKey != col.PropNameSeg && col.JsonSelectKey != col.PropNamePure) ret += $" {col.PropNameSeg}";
                            return ret;
                        };
                        col.SelectValueFunc = alias => alias.IsNotNullOrEmptyOrWhiteSpace() ? $"{alias}.{col.JsonSelectKey}" : $"{col.JsonSelectKey}";
                        continue;
                    }
                    Func<string, string> seg = null;
                    var returning = GetReturn(col.Type);
                    if (returning.IsNotNullOrEmptyOrWhiteSpace())
                    {
                        if (returning.Contains("datetime(6)"))
                        {
                            //使用convert代替,否则查询返回空,反序列化报错
                            seg = alias => $"convert(json_value({columnNameFunc(alias)},{jsonPathWrap}),datetime(6))";
                        }
                        else
                        {
                            seg = alias => $"json_value({columnNameFunc(alias)},{jsonPathWrap} {returning})";
                        }

                    }
                    if (seg == null)
                    {
                        col.SelectFunc = alias => $"json_value({columnNameFunc(alias)},{jsonPathWrap}) {col.PropNameSeg}";
                        col.SelectValueFunc = alias => $"json_value({columnNameFunc(alias)},{jsonPathWrap})";
                    }
                    else
                    {
                        col.SelectFunc = alias => $"{seg(alias)} {col.PropNameSeg}";
                        col.SelectValueFunc = seg;
                    }
                }
            }
            #endregion
        }
        #endregion

        public override string ConvertJsonVariableToSql(string seg, EnumJsonDataType srcType, EnumJsonAcceptAsType destType)
        {
            if (destType == EnumJsonAcceptAsType.DataTableValue) throw new Exception($"不可以将sql语句转为DataTable中的值!");
            //null
            if (srcType == EnumJsonDataType.Null) return seg;
            else if (srcType == EnumJsonDataType.Number)
            {
                //number
                //return cast(1 as json)
                if (destType == EnumJsonAcceptAsType.Doc) return $"cast({seg} as json)";
                //return 1
                else return seg;
            }
            if (srcType == EnumJsonDataType.String)
            {
                //string
                //return '"tom"'
                if (destType == EnumJsonAcceptAsType.Doc) return $"json_quote({seg})";
                //return 'tom'
                else return seg;
            }
            else if (srcType == EnumJsonDataType.Bool)
            {
                //true/false
                //return cast(true as json)
                if (destType == EnumJsonAcceptAsType.Doc) return $"cast({seg} as json)";
                //return true
                else return seg;
            }
            else
            {
                //arr/obj
                //不用区分
                return seg;
            }
        }

        public override string GetDefaultDbType(Type type)
        {
            if (type.IsNullable()) type = Nullable.GetUnderlyingType(type);
            var code = type.GetTypeCode();
            var colType = "json";
            switch (code)
            {
                case TypeCode.SByte:
                case TypeCode.Byte:
                case TypeCode.UInt16:
                case TypeCode.Int16:
                case TypeCode.Int32:
                case TypeCode.UInt32:
                    colType = "int";
                    break;
                case TypeCode.Int64:
                case TypeCode.UInt64:
                    colType = "bigint";
                    break;
                case TypeCode.Double:
                case TypeCode.Single:
                case TypeCode.Decimal:
                    colType = "decimal(30,15)";
                    break;
                case TypeCode.DateTime:
                    colType = "datetime(6)";
                    break;
                case TypeCode.String:
                    colType = "text";
                    break;
                default:
                    break;
            }
            return colType;
        }
    }
}